<%--
  Created by IntelliJ IDEA.
  User: thankyouzero
  Date: 2018/5/2
  Time: 18:47
  To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.regex.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>购票结果</title>
    <link href="layui/css/layui.css" type="text/css" rel="stylesheet" />
    <link href="css/style.css" type="text/css" rel="stylesheet" />
</head>
<body background="img/background.png">
<%
    //连接数据库
    String driver="com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/hbpw?useUnicode=true&characterEncoding=utf-8&useSSL=false";//地址
    String user="hbpwuser";
    String pass="LFFLXY";
    Class.forName(driver);                   //加载jdbc驱动
    Connection connection  = (Connection) DriverManager.getConnection(url,user,pass);

    Statement statement = (Statement) connection.createStatement();  //连接实例
%>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 20px;">
    <legend>购票结果信息</legend>
</fieldset>

<%
    request.setCharacterEncoding("utf-8");//解决编码问题
    String HBH= request.getParameter("HBH");
    String JC_START = request.getParameter("JC_START");
    String JC_END = request.getParameter("JC_END");
    String Cangwei= request.getParameter("Cangwei");
    String date= request.getParameter("date");
    String name = request.getParameter("name");
    String identity = request.getParameter("identity");
    String tkid = request.getParameter("tkid");

    //查询始终机场代码
    String sql1 ="SELECT jc1.JC_DM AS 出发机场代码,jc2.JC_DM  AS 到达机场代码"+
            " FROM jc jc1,jc jc2"+
            " WHERE jc1.JC_MC='"+JC_START+"'"+
            " AND jc2.JC_MC='"+JC_END+"';";
    ResultSet Rst1 = statement.executeQuery(sql1);
    String 出发机场代码=null;
    String 到达机场代码=null;
    while(Rst1.next()){
        出发机场代码= Rst1.getString("出发机场代码");
        到达机场代码= Rst1.getString("到达机场代码");
    }
    Rst1.close();
    //查询涉及到的起始航段顺序号
    String sql2 ="SELECT  s.HD_SXH AS 起飞机场航段号,e.HD_SXH  AS 到达机场航段号"+
            " FROM hd s,hd e"+
            " WHERE"+
            " s.JC_START='"+出发机场代码+"'"+
            " AND e.JC_END='"+到达机场代码+"' AND s.HB_HBH='"+HBH+"' AND e.HB_HBH=s.HB_HBH;";
    ResultSet Rst2 = statement.executeQuery(sql2);
    String 起飞机场航段号=null;
    String 到达机场航段号=null;
    while(Rst2.next()){
        起飞机场航段号= Rst2.getString("起飞机场航段号");
        到达机场航段号= Rst2.getString("到达机场航段号");
    }
    Rst2.close();
    //处理仓位
    String 仓位价格类型=null;
    if(Cangwei.equals("头等舱")) {
        仓位价格类型="HD_TDCJG";
    }
    else{
        仓位价格类型="HD_JJCJG";
    }

    // 大于1: 计算价格 提示购票成功 更新数据库 打印结果
    String 价格=null;
    String gkname=null;

        //计算价格
        String sql4 =" SELECT sum("+仓位价格类型+") AS 价格 FROM hd"+
                " WHERE HD_SXH>="+起飞机场航段号+" AND HD_SXH<="+到达机场航段号+
                " AND HB_HBH='"+HBH+"';";
        ResultSet Rst4 = statement.executeQuery(sql4);
        while(Rst4.next()){
            价格= Rst4.getString("价格");
        }
        Rst4.close();


        //获取用户姓名
        String sql6 =" SELECT GK_XM"+
                " FROM gk WHERE GK_BH='"+identity+"';";
        ResultSet Rst6 = statement.executeQuery(sql6);
        while(Rst6.next()){
            gkname= Rst6.getString("GK_XM");
        }
        Rst6.close();
        //自动增加用户信息
        if(gkname==null)
        {
            String sql8="INSERT INTO gk VALUES('"+
                    identity+"','"+name+"');";
            statement.executeUpdate(sql8);
            gkname = name;
        }
        if(gkname.equals(name))//姓名正确
        {
            if (tkid == null){
                String sql9 ="INSERT INTO spxx(GK_BH,HB_HBH,SPXX_CFJC,SPXX_DDJC,SPXX_CW,SPXX_RQ,SPXX_JG,SPXX_QFHDH,SPXX_DDHDH) " +
                        "VALUES('"+ identity+"','"+HBH+"','"+出发机场代码+"','"+到达机场代码+
                        "','"+Cangwei+"','"+date+"','"+价格+"','"+起飞机场航段号+"','"+到达机场航段号+"');";//"???
                try {
                    statement.executeUpdate(sql9);
                    %>
<blockquote class="layui-elem-quote" style="box-shadow: 2px 2px 5px #5a5a5a">购票成功</blockquote>
<table class="layui-table">
    <thead>
    <tr>
        <th>顾客姓名</th>
        <th>身份证号</th>
        <th>航班号</th>
        <th>出发机场</th>
        <th>到达机场</th>
        <th>仓位</th>
        <th>日期</th>
        <th>价格</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td><%=  name%></td>
        <td><%=  identity%></td>
        <td><%= HBH%></td>
        <td><%= JC_START%></td>
        <td><%= JC_END%></td>
        <td><%= Cangwei%></td>
        <td><%= date%></td>
        <td><%= 价格%></td>
    </tr>
    </tbody>
</table>
<div style="text-align: center">
    <a class="layui-btn layui-btn-primary" href="mgrtk.jsp">查看已完成订单</a>
    <a class="layui-btn" href="index.jsp">返回首页</a>
</div>
<%
                }catch (SQLException e){
                    out.println(sql9);
                    out.println(e);
                }
            }
            else {
                String sql10 ="update spxx set SPXX_CFJC=" +出发机场代码
                                + ",SPXX_DDJC=" + 到达机场代码
                                +",SPXX_CW='"+ Cangwei
                                +"',SPXX_RQ='" +date
                                +"',SPXX_JG="+价格
                                +",SPXX_QFHDH="+起飞机场航段号
                                +",SPXX_DDHDH="+到达机场航段号 + " where tkid=" + tkid + ";";
                try {
                    statement.executeUpdate(sql10);
%>
<blockquote class="layui-elem-quote" style="box-shadow: 2px 2px 5px #5a5a5a">改签成功</blockquote>
<table class="layui-table">
    <thead>
    <tr>
        <th>顾客姓名</th>
        <th>身份证号</th>
        <th>航班号</th>
        <th>出发机场</th>
        <th>到达机场</th>
        <th>仓位</th>
        <th>日期</th>
        <th>价格</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td><%=  name%></td>
        <td><%=  identity%></td>
        <td><%= HBH%></td>
        <td><%= JC_START%></td>
        <td><%= JC_END%></td>
        <td><%= Cangwei%></td>
        <td><%= date%></td>
        <td><%= 价格%></td>
    </tr>
    </tbody>
</table>
<%
                }catch (SQLException e){
                    out.println(sql10);
                    out.println(e);
                }
            }
        }
        else {
%>
<blockquote class="layui-elem-quote layui-quote-nm">姓名错误！</blockquote>
<%
        }
    statement.close();
    connection.close();
%>







</body>
</html>
